"""
@author: xiangping
@contact: xiangpingbu@gmail.com
@time: 2019/12/28 5:58 下午
@file: gaolu_biz_service.py
@Desc:
"""
import collections
import datetime
import json
import logging as log
import time
from typing import List, Any, Tuple, Union

import pandas as pd
from flask import current_app

from app.database import convert
from app.exts import data_container, ironman_redis as redis, mysql_pool
from app.exts import ironman_redis
from app.models.bo.gaolu.gaolu_input_param_bo import GaoLuInputParamBo
from app.models.bo.gaolu.gaolu_model_input_bo import GaoLuModelInputBO
from app.models.bo.gaolu.gaolu_reason_input_bo import GaoLuReasonInputBO
from app.models.bo.gaolu.gaolu_reason_status_bo import GaoLuReasonStatusBO
from app.models.po.gaolu import gaolu_model
from app.models.po.gaolu.gaolu_banbie_score_po import GaoLuBanBieScorePO
from app.models.po.gaolu.gaolu_model_input_po import GaoLuModelInputPO
from app.models.po.gaolu.gaolu_predict_po import GaoLuPredictPO
from app.services.dal.gaolu_dao_service import gaolu_history_between, gaolu_history_between_json, get_gaolu_result_one, \
    gaolu_model_input_one, gaolu_insert_predict_result, select_simulate_input, insert_input_po, delete_from_to_predict, \
    delete_from_to_input, get_latest_input_cache, gaolu_banbie_history_between_query, gaolu_insert_banbie_score
from app.services.models.const import NORMAL_CODE, GOOD_SCORE_LEVEL
from app.services.models.gaolu_reason_model import gaolu_reason_model
from app.services.models.gaolu_score_model import gaolu_score_model
from app.utils import cal_util
from app.utils.sys_utils import get_list_intersect, get_time_round_and_adjust, copy_properties


def gaolu_calculate_dist(start, end, df, bin_num):
    # df = df[(start < df['DateTime']) & (df['DateTime'] < end)]
    param_list = GaoLuInputParamBo.exposed_param_list()
    df = df[param_list]
    return calculate_dist(df, bin_num)


def calculate_dist(df, bin_num):
    """
    生成参数分布
    :param df:
    :param bin_num:
    :return:
    """
    dist_dict_outer = {}
    for col in df.columns:
        param = pd.cut(df[col], bins=bin_num, retbins=True, include_lowest=True)
        param_norm = param[0].value_counts(sort=False, normalize=True).mul(100).array
        param_bins = param[1]
        bin_median_list = []
        for i in range(bin_num):
            # 保留两位小数
            bin_median_list.append(round(((param_bins[i] + param_bins[i + 1]) / 2), 2))
        dist_dict_inner = dict(zip(bin_median_list, param_norm))
        dist_dict_outer[col] = dist_dict_inner
    return dist_dict_outer


def gaolu_calculate_quantile(start, end, df):
    """
    生成参数percentile 结果
    :param start:
    :param end:
    :param df:
    :return:
    """
    df = df[(start < df['DateTime']) & (df['DateTime'] < end)]
    # df = df.loc[:, ~(df.columns.str.contains('status$', case=False) | df.columns.str.contains('DateTime', case=False))]
    param_list = GaoLuInputParamBo.exposed_param_list()
    df = df[param_list]
    return calculate_quantile(df)


def calculate_quantile(df):
    df_quantile = pd.DataFrame()
    for i in range(1, 101):
        df_quantile = df_quantile.append(df.quantile(i / 100))
    return df_quantile.to_dict()


def param_dist_dict(start, end, refresh=False):
    """
    :param start:
    :param end:
    :param refresh: 是否刷新 data_container里面的数据
    :return:
    """
    if refresh:
        reload_data_container(key='gaolu_param_distribution', bin_num=None, start=start, end=end)

    try:
        if start is None and end is None:
            param_dist_dict_json = data_container['gaolu_param_distribution']
        else:
            start_str = datetime.datetime.strftime(start, "%Y-%m-%d")
            end_str = datetime.datetime.strftime(end, "%Y-%m-%d")
            param_dist_dict_json = ironman_redis.load("param_dist_dic_" + start_str + "_" + end_str)
        return param_dist_dict_json
    except Exception as e:
        current_app.logger.info(e)


def param_quantile_dict(start, end, refresh=False):
    """
    :param start:
    :param end:
    :param refresh: 是否刷新 data_container里面的数据
    :return:
    """
    if refresh:
        reload_data_container(key='gaolu_param_quantile', start=start, end=end)

    try:
        param_quantile_dict_json = json.dumps(data_container['gaolu_param_quantile'])
        return param_quantile_dict_json
    except Exception as e:
        current_app.logger.info(e)


def gaolu_lose_status_percentage(start: datetime, end: datetime):
    """
    获得各个参数在start 至 end （闭区间）内，异常出现个数占比, 按异常多发顺序返回
    :param start:
    :param end:
    :return:
    """
    history = gaolu_history_between(start, end, left="close", right="close")
    needed_param_list = None
    ret = {}
    total_num_records = len(history)
    for predict_po in history:
        if needed_param_list is None:
            # 获取需要字段
            needed_param_list = list(
                set(history[0].__dir__()).intersection(set(GaoLuReasonStatusBO.exposed_param_list())))
        for param_name in needed_param_list:
            ret[param_name] = ret.get(param_name) if ret.get(param_name) is not None else 0.0
            param_value = predict_po.__getattribute__(param_name)
            if param_value != NORMAL_CODE:
                v = ret.get(param_name)
                ret[param_name] = (v + 100 / total_num_records)

    sorted_ret = list(filter(lambda x: (x[1] > 0), list(ret.items())))
    sorted_ret = list(map(lambda x: (GaoLuReasonStatusBO.member_mapping_dict[x[0]], x[1]), sorted_ret))
    sorted_ret.sort(key=lambda x: x[1], reverse=True)
    return sorted_ret


def reload_data_container(key, bin_num, start=None, end=None):
    if bin_num is None:
        bin_num = current_app.config['GAOLU_PARAM_HISTORY_BINS']

    if 'gaolu_param_distribution' == key:
        history = gaolu_history_between_json(start, end)
        history_df = pd.read_json(history)
        dist_dict = gaolu_calculate_dist(start=start,
                                         end=end,
                                         df=history_df,
                                         bin_num=bin_num)

        start_str = datetime.datetime.strftime(start, "%Y-%m-%d")
        end_str = datetime.datetime.strftime(end, "%Y-%m-%d")
        key = "param_dist_dic_" + start_str + "_" + end_str
        ironman_redis.dump(key, dist_dict)

        data_container['gaolu_param_distribution'] = dist_dict

    if 'gaolu_param_quantile' == key:
        history = gaolu_history_between_json(start, end)
        history_df = pd.read_json(history)
        quantile_dict = gaolu_calculate_quantile(start=start,
                                                 end=end,
                                                 df=history_df)
        data_container['gaolu_param_quantile'] = quantile_dict


def auto_score(last, current):
    cur_record: GaoLuPredictPO = get_gaolu_result_one(current)
    ret_dict = {}
    if cur_record is not None:
        score_dict = get_auto_score_by_record(cur_record)
        ret_dict.update(score_dict)
        # 保存最新记录的时间点
        # redis.set('auto_score', current.strftime("%Y-%m-%d %H:%M:%S"))
    last_record = get_gaolu_result_one(last)
    if last_record is not None:
        last_score_dict = {'CG_LT_GL_GL04_Score': last_record.CG_LT_GL_GL04_Score,
                           'CG_LT_GL_GL04_Status': last_record.CG_LT_GL_GL04_Status,
                           'DateTime': last_record.DateTime.strftime("%Y-%m-%d %H:%M:%S")}
        ret_dict['lastHourScore'] = last_score_dict
    # if cur_record is None:
    #     # 如果当前时间点记录找不到，获取最近有记录的时间点
    #     try:
    #         lasted_time = str(redis.get('auto_score'), encoding='utf-8')
    #         lasted_time = datetime.datetime.strptime(lasted_time, "%Y-%m-%d %H:%M:%S")
    #         lasted_record = get_gaolu_result_one(lasted_time)
    #         score_dict = get_auto_score_by_record(lasted_record)
    #         ret_dict.update(score_dict)
    #     except Exception as e:
    #         pass
    desc = ''
    if cur_record is not None:
        desc = '较平稳'
        count = 0
        t1 = cur_record.CG_LT_GL_GL04_Score

        t2 = None
        t3 = None
        t4 = None
        # t2 = redis.get("gaolu_score_trend_" + str((current + datetime.timedelta(hours=-1)).hour))
        # t3 = redis.get("gaolu_score_trend_" + str((current + datetime.timedelta(hours=-2)).hour))
        # t4 = redis.get("gaolu_score_trend_" + str((current + datetime.timedelta(hours=-3)).hour))

        if t2 is None: t2 = 0
        if t3 is None: t3 = 0
        if t4 is None: t4 = 0

        if t3 > t2 and t2 > t1:
            desc = '下行'
        elif t3 < t2 and t2 < t1:
            desc = '上行'
        else:
            if (abs(t4 - t3) > t4 * 0.15): count = count + 1
            if (abs(t3 - t2) > t3 * 0.15): count = count + 1
            if (abs(t2 - t1) > t2 * 0.15): count = count + 1

            if count == 3: desc = '较大波动'
            if count == 2: desc = '较小波动'
            if count == 1: desc = '轻微波动'

        gaolu_reason_input_bo = GaoLuReasonInputBO()
        copy_properties(cur_record, gaolu_reason_input_bo)
        ret_dict['reasonScope'] = gaolu_reason_model.get_reason_scope(gaolu_reason_input_bo)

    ret_dict['desc'] = desc

    return ret_dict


def get_auto_score_by_record(record: GaoLuPredictPO):
    needed_status_list = get_list_intersect(record.__dir__(), GaoLuReasonStatusBO.exposed_param_list())
    status_dict = collections.OrderedDict()

    """
    分数大于85， 全部设为正常
    TODO：该逻辑从算法角度应该修改
    前端需求， 异常值放在前面
    """
    cur_score2 = record.CG_LT_GL_GL04_Score if record.CG_LT_GL_GL04_Score is not None else 0
    normal_status_dict = {}
    abnormal_status_dict = {}
    for param_name in needed_status_list:
        status = record.__getattribute__(param_name)
        if status != NORMAL_CODE and cur_score2 < GOOD_SCORE_LEVEL:
            abnormal_status_dict[param_name] = status
        else:
            normal_status_dict[param_name] = status if cur_score2 < GOOD_SCORE_LEVEL else NORMAL_CODE

    status_dict.update(abnormal_status_dict, **normal_status_dict)
    needed_value_list = get_list_intersect(record.__dir__(), GaoLuInputParamBo.exposed_param_list())
    value_dict = {}
    for param_name in needed_value_list:
        value_dict[param_name] = round(
            record.__getattribute__(param_name), 2) if record.__getattribute__(
            param_name) is not None else None

    cur_score_dict = {'CG_LT_GL_GL04_Score': record.CG_LT_GL_GL04_Score,
                      'CG_LT_GL_GL04_Status': record.CG_LT_GL_GL04_Status,
                      'DateTime': record.DateTime.strftime("%Y-%m-%d %H:%M:%S")}
    stage_warning = "暂无"
    lfll = record.CG_LT_GL_GL04_LFLL
    if lfll:
        if lfll > 5000:
            stage_warning = '正常炉况'
        elif lfll > 4800 and lfll <= 5000:
            stage_warning = "四级预警"
        elif lfll > 4600 and lfll <= 4800:
            stage_warning = "三级预警"
        elif lfll > 4200 and lfll <= 4600:
            stage_warning = "二级预警"
        else:
            stage_warning = "一级预警"

    ret_dict = {'curScore': cur_score_dict,
                'value': value_dict,
                'status': status_dict,
                'statusDict': GaoLuReasonStatusBO.exposed_param_dict(),
                'valueDict': GaoLuInputParamBo.exposed_param_dict(),
                'statusWarning': record.status_warning,
                'stageWarning': stage_warning}
    return ret_dict


def get_daily_score(date: datetime):
    start = get_time_round_and_adjust(date, step=0, round_level="days")
    end = get_time_round_and_adjust(date, step=1, round_level="days")
    history = gaolu_history_between(start, end, left="close", right="open")
    history = list(filter(lambda x: x.CG_LT_GL_GL04_Score is not None, history))

    shift_score = 0.0
    for predict_po in history:
        score = predict_po.CG_LT_GL_GL04_Score
        shift_score += score

    shift_score = shift_score / len(history) if len(history) != 0 else -9999

    ret = {'score': shift_score,
           'comment': get_ban_bie_comment(shift_score) if shift_score >= 0 else "无有效数据"}
    return ret


def get_ban_bie_score(date: datetime):
    """
    :param date:
    :return:
    时间为
        morning:（00：08]
        noon:（08：16]
        night:（16：00]
    只接受date 参数，认为该date 已有结果，
    如果没有可用数据，返回-9999 分数
    """
    start = get_time_round_and_adjust(date, step=0, round_level="days")
    morning = start + datetime.timedelta(hours=8)
    noon = morning + datetime.timedelta(hours=8)
    night = noon + datetime.timedelta(hours=8)

    result = {}
    morning_score, ret = get_avg_score(start, morning)
    morning_ret = {
        'score': morning_score,
        'comment': get_ban_bie_comment(morning_score) if morning_score != -9999 else "无有效数据",
        'banci': ret
    }
    result['MO'] = morning_ret

    noon_score, ret = get_avg_score(morning, noon)
    noon_ret = {
        'score': noon_score,
        'comment': get_ban_bie_comment(noon_score) if noon_score != -9999 else "无有效数据",
        'banci': ret
    }
    result['NO'] = noon_ret

    night_score, ret = get_avg_score(noon, night)
    night_ret = {
        'score': night_score,
        'comment': get_ban_bie_comment(night_score) if night_score != -9999 else "无有效数据",
        'banci': ret
    }
    result['NI'] = night_ret

    return result


def get_shift_score(start, end) -> Union[Tuple[int, None], Tuple[float, str]]:
    ret: List[GaoLuBanBieScorePO] = get_gaolu_banbie_history_between(start, end, left="close", right="open")
    current_ban_bie_score = score_of_index(ret, start, end)

    next_ret: List[GaoLuBanBieScorePO] = get_gaolu_banbie_history_between(end, end + datetime.timedelta(hours=8),
                                                                          left="close",
                                                                          right="open")
    next_ban_bie_score = score_of_index(next_ret, end, end + datetime.timedelta(hours=8))
    if current_ban_bie_score == -9999 or len(ret) < 8:
        return -9999, None
    if next_ban_bie_score == -9999:
        next_ban_bie_score = 0.0
    banci = None
    for predict_po in ret:
        banci = predict_po.banci
        if banci is not None:
            break
    return current_ban_bie_score * 0.7 + next_ban_bie_score * 0.3, banci


def get_avg_score(start, end) -> Union[Tuple[int, None], Tuple[float, str]]:
    ret: List[GaoLuBanBieScorePO] = get_gaolu_banbie_history_between(start, end, left="close", right="open")
    history = gaolu_history_between(start, end, left="close", right="close")
    if ret is None: return -9999, None

    avg_score = cal_util.avg([entity.CG_LT_GL_GL04_Score for entity in history])

    banci = None
    for predict_po in ret:
        banci = predict_po.banci
        if banci is not None:
            break

    return avg_score, banci


def score_of_index(ret: List[GaoLuBanBieScorePO], start: datetime.datetime, end: datetime.datetime):
    if len(ret) < 1:
        return -9999
    BFBatch = get_BFBatch(ret)
    BTQXZS = get_BTQXZS(ret)
    BV = get_BV(ret)
    return BFBatch + BTQXZS + BV


def get_BFBatch(history: List[GaoLuBanBieScorePO]):
    score = 0.0
    BFBatch = 0.0
    for predict_po in history:
        BFBatch += predict_po.FBatchHour
    if BFBatch == 49.5:
        score = 65
    elif BFBatch == 49 or BFBatch == 50:
        score = 55
    elif BFBatch == 48.5 or BFBatch == 50.5:
        score = 45
    elif BFBatch == 48 or BFBatch == 51:
        score = 35
    elif BFBatch == 47.5 or BFBatch == 51.5:
        score = 25
    elif BFBatch == 47 or BFBatch == 52:
        score = 15
    elif BFBatch == 46.5 or BFBatch == 52.5:
        score = 5
    elif BFBatch <= 46 or BFBatch >= 53:
        score = 0
    return score


def get_BTQXZS(history: List[GaoLuBanBieScorePO]):
    score = 0.0
    sum_of_TQXZS = 0.0
    for predict_po in history:
        sum_of_TQXZS += predict_po.CG_LT_GL_GL04_TQXZS
    BTQXZS = sum_of_TQXZS / len(history)  # average
    if BTQXZS >= 32:
        score = 15
    elif 31 <= BTQXZS < 32:
        score = 10
    elif 30 <= BTQXZS < 31:
        score = 5
    else:
        score = 0
    return score


def get_BV(history: List[GaoLuBanBieScorePO]):
    score = 0.0
    sum_of_V = 0.0
    for predict_po in history:
        sum_of_V += predict_po.CG_LT_GL_GL04_V
    BV = sum_of_V / len(history)  # average
    if BV >= 0.245:
        score = 20
    elif 0.24 <= BV < 0.245:
        score = 15
    elif 0.235 <= BV < 0.24:
        score = 10
    elif 0.23 <= BV < 0.235:
        score = 5
    else:
        score = 0
    return score


# def get_shift_score(start, end):
#     ret = gaolu_history_between(start, end, left="open", right="close")
#     shift_score = 0.0
#     shift_weight = 0.0
#     last = -9999.0
#     ret = list(filter(lambda x: x.CG_LT_GL_GL04_Score is not None, ret))
#     for predict_po in ret:
#         score = predict_po.CG_LT_GL_GL04_Score
#         shift_score += score
#         if -9999.0 != last:
#             if last > score:
#                 shift_weight -= 1.0
#             elif last < score:
#                 shift_weight += 1.0
#
#     shift_score = -9999 if len(ret) == 0 else shift_score / len(ret) + shift_weight
#     return shift_score


def get_ban_bie_comment(score):
    if score >= 90:
        return "优秀"
    elif 80 <= score < 90:
        return "良好"
    elif 60 <= score < 80:
        return "及格"
    else:
        return "不及格"


def get_gaolu_score(gaolu_model_input_bo: GaoLuModelInputBO) -> float:
    gaolu_score_model.gaolu_model_input_bo = gaolu_model_input_bo
    return gaolu_score_model.process()


def get_gaolu_reason(gaolu_reason_input_bo: GaoLuReasonInputBO):
    return gaolu_reason_model.process(gaolu_reason_input_bo)


# 可用于实际和模拟环境
def model_predict(simulate_time_label=None):
    if simulate_time_label is None:
        query_time = datetime.datetime.now()
    else:
        query_time = simulate_time_label

    gaolu_query_models(query_time, True)


def gaolu_query_models(query_time, is_persist=False):
    """
    启动两个模型， 查询 并入库 if is_persisit == True
    :param query_date:
    :param isPersist:
    :return: gaolu_predict_po
    """
    """
    # 线上环境，有可能从各个系统直接获取模型参数，而不是查询现有表来得到参数
     线上环境，也会先生成   gaolu_model_input表，由外围程序处理
    """
    date = query_time.date().strftime("%Y-%m-%d")
    hour = query_time.hour
    gaolu_model_input_po: GaoLuModelInputPO = gaolu_model_input_one(date, hour)
    if gaolu_model_input_po is None:
        return None
    gaolu_model_input_bo = GaoLuModelInputBO()
    copy_properties(gaolu_model_input_po, gaolu_model_input_bo)
    gaolu_model_score_bo = get_gaolu_score(gaolu_model_input_bo)

    gaolu_reason_input_bo = GaoLuReasonInputBO()
    copy_properties(gaolu_model_input_po, gaolu_reason_input_bo)
    gaolu_reason_status_bo = get_gaolu_reason(gaolu_reason_input_bo)
    gaolu_predict_po = GaoLuPredictPO()

    copy_properties(gaolu_model_input_po, gaolu_predict_po)
    copy_properties(gaolu_model_score_bo, gaolu_predict_po)
    copy_properties(gaolu_reason_status_bo, gaolu_predict_po)

    gaolu_predict_po.GMT_CREATE = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    if isinstance(gaolu_model_input_po.FDate, str):
        gaolu_predict_po.DateTime = datetime.datetime.strptime(gaolu_model_input_po.FDate,
                                                               "%Y-%m-%d") \
                                    + datetime.timedelta(hours=gaolu_model_input_po.FHour)
    else:
        gaolu_predict_po.DateTime = datetime.datetime.strptime(gaolu_model_input_po.FDate.strftime("%Y-%m-%d %H:%M:%S"),
                                                               "%Y-%m-%d %H:%M:%S") \
                                    + datetime.timedelta(hours=gaolu_model_input_po.FHour)

        redis.set("gaolu_score_point_" + hour, gaolu_predict_po.CG_LT_GL_GL04_Score)
    if is_persist:
        # id 自增
        gaolu_predict_po.id = None
        gaolu_predict_po = gaolu_insert_predict_result(gaolu_predict_po)
    return gaolu_predict_po


def get_gaolu_history_between(start, end, left='close', right='close', order_by=None) \
        -> List[GaoLuPredictPO]:
    return gaolu_history_between(start, end, left, right, order_by=order_by)


def gaolu_predict_clean_up(start_time, end_time):
    return delete_from_to_predict(start_time, end_time)


def gaolu_input_clean_up(start_time, end_time):
    return delete_from_to_input(start_time, end_time)


def gaolu_clean_up(start_time, end_time):
    gaolu_input_clean_up(start_time, end_time)
    gaolu_predict_clean_up(start_time, end_time)


def simulate_data_stream(query_time, label_time=None):
    simulate_input_po = select_simulate_input(query_time)
    input_bo = GaoLuModelInputPO()
    copy_properties(simulate_input_po, input_bo)
    if label_time is None:
        input_bo.FDate = datetime.datetime.now().date().strftime("%Y-%m-%d")
        input_bo.FHour = datetime.datetime.now().hour
        input_bo.id = None
    else:
        input_bo.FDate = label_time.date().strftime("%Y-%m-%d")
        input_bo.FHour = label_time.hour
        input_bo.id = None
    input_bo.GMT_CREATE = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    insert_input_po(input_bo)


def gaolu_history_data_transform(o: Any):
    """
    历史数据做转换
    :return:
    """

    mapping_dict = GaoLuInputParamBo.member_mapping_dict
    from collections import OrderedDict
    # 排除元属性
    exclude = [i for i in o.__dir__() if i.startswith('_')] + ['metadata', 'query', 'query_class']
    ret_dict = o.__dict__
    for item in exclude:
        ret_dict.pop(item, None)
    ordered = OrderedDict()

    ordered['时间'] = ret_dict['DateTime'].strftime("%Y-%m-%d %H:%M:%S")
    ordered['分数'] = ret_dict['CG_LT_GL_GL04_Score']
    ordered['状态'] = ret_dict['CG_LT_GL_GL04_Status']

    for k, v in ret_dict.items():
        if k in mapping_dict and k not in GaoLuInputParamBo.exclude_list:
            name = mapping_dict.get(k)  # 获取中文解释
            # 中文解释作为key
            ordered[name] = v
            ordered[name + "状态"] = ret_dict.get(k + "_status")

    # l = list()
    # for k, v in ret_dict.items():
    #     if k in mapping_dict and k not in GaoLuInputParamBo.exclude_list :
    #         d = dict()
    #         d['title'] = mapping_dict.get(k)
    #         d['dataIndex'] = k
    #         l.append(d)

    return ordered


# 可用对接 Kafka redis
def model_predict_online():
    gaolu_query_models_online()


# 可用对接 Kafka redis
def gaolu_query_models_online(is_persist=True):
    """
    启动两个模型， 查询 并入库 if is_persisit == True
    :param isPersist:
    :return: gaolu_predict_po
    """

    # 从redis中获取字段
    gaolu_model_input_po: GaoLuModelInputPO = get_latest_input_cache()

    CG_LT_GL_GL04_DW = None
    CG_LT_GL_GL04_ZXQL = None  # T阀座温度/T顶温
    CG_LT_GL_GL04_XBYC = None
    CG_LT_GL_GL04_SBYC = None
    CG_LT_GL_GL04_YC = None
    CG_LT_GL_GL04_XBYCZB = None
    CG_LT_GL_GL04_ZBYCZB = None
    CG_LT_GL_GL04_SBYCZB = None
    CG_LT_GL_GL04_MQLYL = None

    D01 = gaolu_model_input_po.CG_LT_GL_GL04_LDWD01
    D02 = gaolu_model_input_po.CG_LT_GL_GL04_LDWD02
    D03 = gaolu_model_input_po.CG_LT_GL_GL04_LDWD03
    D04 = gaolu_model_input_po.CG_LT_GL_GL04_LDWD04
    FZWD = gaolu_model_input_po.CG_LT_GL_GL04_FZWD
    XBYC01 = gaolu_model_input_po.CG_LT_GL_GL04_XBYC01
    XBYC02 = gaolu_model_input_po.CG_LT_GL_GL04_XBYC01
    XBYC03 = gaolu_model_input_po.CG_LT_GL_GL04_XBYC01
    XBYC04 = gaolu_model_input_po.CG_LT_GL_GL04_XBYC01
    SBYC01 = gaolu_model_input_po.CG_LT_GL_GL04_SBYC01
    SBYC02 = gaolu_model_input_po.CG_LT_GL_GL04_SBYC02
    SBYC03 = gaolu_model_input_po.CG_LT_GL_GL04_SBYC03
    SBYC04 = gaolu_model_input_po.CG_LT_GL_GL04_SBYC04
    RFYL02 = gaolu_model_input_po.CG_LT_GL_GL04_RFYL02
    LDYL = gaolu_model_input_po.CG_LT_GL_GL04_LDYL
    COZXFX = gaolu_model_input_po.CG_LT_GL_GL04_COZXFX
    CO2ZXFX = gaolu_model_input_po.CG_LT_GL_GL04_CO2ZXFX

    if D01 and D02 and D03 and D04:
        CG_LT_GL_GL04_DW = 0.25 * (D01 + D02 + D03 + D04)

    if CG_LT_GL_GL04_DW and FZWD:
        CG_LT_GL_GL04_ZXQL = float(FZWD / CG_LT_GL_GL04_DW)

    if XBYC01 and XBYC02 and XBYC03 and XBYC04:
        CG_LT_GL_GL04_XBYC = 0.25 * (XBYC01 + XBYC02 + XBYC03 + XBYC04)

    if SBYC01 and SBYC02 and SBYC03 and SBYC04:
        CG_LT_GL_GL04_SBYC = 0.25 * (SBYC01 + SBYC02 + SBYC03 + SBYC04)

    if RFYL02 and LDYL:
        CG_LT_GL_GL04_YC = float(RFYL02 - LDYL)

    if CG_LT_GL_GL04_XBYC and CG_LT_GL_GL04_YC:
        CG_LT_GL_GL04_XBYCZB = float(CG_LT_GL_GL04_XBYC / CG_LT_GL_GL04_YC)

    if CG_LT_GL_GL04_XBYC and CG_LT_GL_GL04_SBYC:
        CG_LT_GL_GL04_ZBYCZB = 1 - CG_LT_GL_GL04_XBYC - CG_LT_GL_GL04_SBYC

    if CG_LT_GL_GL04_SBYC and CG_LT_GL_GL04_YC:
        CG_LT_GL_GL04_SBYCZB = float(CG_LT_GL_GL04_SBYC / CG_LT_GL_GL04_YC)

    if COZXFX and CO2ZXFX:
        CG_LT_GL_GL04_MQLYL = float(CO2ZXFX / (COZXFX + CO2ZXFX))

    gaolu_model_input_po.CG_LT_GL_GL04_DW = CG_LT_GL_GL04_DW
    gaolu_model_input_po.CG_LT_GL_GL04_ZXQL = CG_LT_GL_GL04_ZXQL
    gaolu_model_input_po.CG_LT_GL_GL04_XBYC = CG_LT_GL_GL04_XBYC
    gaolu_model_input_po.CG_LT_GL_GL04_SBYC = CG_LT_GL_GL04_SBYC
    gaolu_model_input_po.CG_LT_GL_GL04_YC = CG_LT_GL_GL04_YC
    gaolu_model_input_po.CG_LT_GL_GL04_XBYCZB = CG_LT_GL_GL04_XBYCZB
    gaolu_model_input_po.CG_LT_GL_GL04_ZBYCZB = CG_LT_GL_GL04_ZBYCZB
    gaolu_model_input_po.CG_LT_GL_GL04_SBYCZB = CG_LT_GL_GL04_SBYCZB
    gaolu_model_input_po.CG_LT_GL_GL04_MQLYL = CG_LT_GL_GL04_MQLYL

    if gaolu_model_input_po is None:
        return None
    gaolu_model_input_bo = GaoLuModelInputBO()
    copy_properties(gaolu_model_input_po, gaolu_model_input_bo)
    log.info(json.dumps(gaolu_model_input_bo.get_value_dict()))

    gaolu_model_score_bo = get_gaolu_score(gaolu_model_input_bo)

    gaolu_reason_input_bo = GaoLuReasonInputBO()
    copy_properties(gaolu_model_input_po, gaolu_reason_input_bo)
    gaolu_reason_status_bo = get_gaolu_reason(gaolu_reason_input_bo)
    gaolu_predict_po = GaoLuPredictPO()

    copy_properties(gaolu_model_input_po, gaolu_predict_po)
    copy_properties(gaolu_model_score_bo, gaolu_predict_po)
    copy_properties(gaolu_reason_status_bo, gaolu_predict_po)

    gaolu_predict_po.GMT_CREATE = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    gaolu_predict_po.FBatchHour = gaolu_model_input_po.CG_LT_GL_FBatchHour
    date = None
    if gaolu_model_input_po.FDate is None:
        gaolu_predict_po.DateTime = None
    elif isinstance(gaolu_model_input_po.FDate, str):
        date = datetime.datetime.strptime(gaolu_model_input_po.FDate, "%Y-%m-%d")
        gaolu_predict_po.DateTime = date + datetime.timedelta(hours=gaolu_model_input_po.FHour)
    else:
        date = gaolu_model_input_po.FDate
        gaolu_predict_po.DateTime = date + datetime.timedelta(hours=gaolu_model_input_po.FHour)

    hour = gaolu_predict_po.FHour
    range = None
    if 1 <= hour <= 8:
        gaolu_predict_po.banbie = '早班'
        # 确认区间
        range = [date + datetime.timedelta(hours=1), date + datetime.timedelta(hours=8)]
    if 9 <= hour <= 16:
        gaolu_predict_po.banbie = '中班'
        range = [date + datetime.timedelta(hours=9), date + datetime.timedelta(hours=16)]
    if hour >= 17 or hour == 0:
        gaolu_predict_po.banbie = '晚班'
        range = [date + datetime.timedelta(hours=17), date + datetime.timedelta(hours=24)]

    chutie = convert.query(gaolu_model.GaoLuChuTie,
                           gaolu_model.GaoLuChuTie.fdatetime.between(range[0], range[1])) \
        .order_by(gaolu_model.GaoLuChuTie.fdatetime.desc()).first()
    if chutie:
        gaolu_predict_po.banci = chutie.fbc

    gaolu_banbie_score_po = GaoLuBanBieScorePO()
    copy_properties(gaolu_predict_po, gaolu_banbie_score_po)

    if is_persist:
        # id 自增
        gaolu_predict_po.id = None
        gaolu_predict_po = gaolu_insert_predict_result(gaolu_predict_po)
        gaolu_insert_banbie_score(gaolu_banbie_score_po)
    return gaolu_predict_po


def get_gaolu_banbie_history_between(start, end, left='close', right='close', order_by=None) \
        -> List[GaoLuBanBieScorePO]:
    if end is None or start is None:
        return None
    ret = gaolu_banbie_history_between_query(start, end, left, right, order_by=order_by).all()
    return ret


def gaolu_banbie_backtracking_data():
    """
    回溯上一个班打分信息，并对旧数据更新
    :return:
    """
    current_app.logger.info("[GaoLu BanBie Score] backtracking data. date: {}".format(
        datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
    time_window_end = get_time_round_and_adjust(datetime.datetime.now(), step=0, round_level="hour")

    # 上一班次
    time_window_start = time_window_end - datetime.timedelta(hours=8)
    his_ret = get_gaolu_banbie_history_between(time_window_start, time_window_end, left="close", right="open",
                                               order_by=GaoLuBanBieScorePO.GMT_CREATE.asc())

    # syn_tq_rshangliaoqk表回溯的时候需要fdate和fhour两个字段进行查询。因为数据是人工后续添上的, 所以fdaetime不准
    for record in his_ret:
        gmt_crete = get_time_round_and_adjust(record.GMT_CREATE, step=0, round_level='hour')
        if record.DateTime != gmt_crete:
            record.DateTime = gmt_crete
        record_datetime = record.DateTime
        fdate = datetime.datetime(record_datetime.year, record_datetime.month, record_datetime.day)
        fhour = record_datetime.hour
        # ShangLiaoQk表中当天凌晨12点为前一天的24时
        if record_datetime.hour == 0:
            fdate = get_time_round_and_adjust(record_datetime, step=-1, round_level="days")
            fhour = 24
        shangliaoqk = convert.query(gaolu_model.ShangLiaoQk, fdate=fdate, fhour=fhour) \
            .order_by(gaolu_model.ShangLiaoQk.fdatetime.asc()).first()
        if shangliaoqk is not None:
            record.FBatchHour = shangliaoqk.fbatchhour
            convert.update_model(record, update=None)


def get_daily_score_by_range(start, end):
    """

    :param start:
    :param end:
    :return:
    """
    conn, cursor = mysql_pool.connect("ironman")
    sql = """
    select any_value(DateTime) as date_time,any_value(avg(CG_LT_GL_GL04_Score)) as score from cg_lt_gl_gl04_gaolu_predict where DateTime >='{}'
    and DateTime <='{}' group by year(`DateTime`),month(`DateTime`),day(`DateTime`)
    """.format(start, end)

    cursor.execute(sql)
    results = cursor.fetchall()

    mysql_pool.connect_close(conn, cursor)

    return results
